package DBLag;
import ModelLag.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * Databaseklasse for Locker som står for kommunikationen til databasen og som står
 * for opbygningen af Locker objekter som skal bruges i de højere lag.
 * @author DM75 Gruppe 3
 * @version 2011-12-22
 */
public class DbLocker implements IFDbLocker
{
    //Forbindelse til databasen.
    private Connection con;
    PreparedStatement pStmtSelect;
    private boolean debug = false;
    
    public DbLocker()
    {
        con = DbConnection1.getInstance().getDBcon();
        pStmtSelect = null;
    }
    
    public Locker findLocker(int lockerID)
    {
        if(pStmtSelect == null)
        {
            pStmtSelect = getPreparedStmt();
        }    
        Locker lockerObj = null;
        ResultSet results;
        
        try
        {
            pStmtSelect.setInt(1, lockerID);
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                lockerObj = buildLocker(results);
            }
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return lockerObj; 
    }
    
    /**
     * Opretter en liste med alle de lockerObjs som er udlejet.
     * @param retriveAssociation true = Associering til Customer.
     * @return ArrayList med udlejede lockerObjs.
     */
    public ArrayList<Locker> getRentetLockers(boolean retriveAssociation)
    {
        ArrayList<Locker> list = new ArrayList<Locker>();
        PreparedStatement pstmt = null;
        
        String query = "SELECT Locker.lockerID, available, depositum, pricePrYear, amountOfKeys, lockerType, fName, lName, Customer.phoneNo "
                     + "FROM Locker, Customer, LockerCustomer "
                     + "WHERE Locker.lockerID = LockerCustomer.lockerID AND Customer.phoneNo = LockerCustomer.phoneNo";
        if(debug)
        {
            System.out.println(query);
        }
        
        ResultSet results = null;
        Locker lockerObj = null;
        
        try
        {
            pstmt = con.prepareStatement(query);
            pstmt.setQueryTimeout(5);
            results = pstmt.executeQuery();
            
            while(results.next())
            {
                lockerObj = buildLocker(results);
                
                if(retriveAssociation)
                {
                    IFDbCustomer dbCustomer = new DbCustomer();
                    Customer customerObj = dbCustomer.findCustomer(results.getString("phoneNo"));
                    lockerObj.setCustomer(customerObj);
                }
                list.add(lockerObj);
            }
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return list; 
    }
    /**
     * Opretter en list med alle de lockerObjs som IKKE er udlejet.
     * @return ArrayList med Locker som ikke er udlejet.
     */
    public ArrayList<Locker> getFreeLockers()
    {
        ArrayList<Locker> list = new ArrayList<Locker>();
        PreparedStatement pstmt = null;
        
        String freeLockers = "SELECT Locker.lockerID, available, depositum, pricePrYear, amountOfKeys, lockerType "
                           + "FROM Locker "
                           + "WHERE Locker.lockerID NOT IN (SELECT lockerID "
                                                         + "FROM LockerCustomer)";
        if(debug)
        {
            System.out.println(freeLockers);
        }
        
        Locker lockerObj = null;
        ResultSet results;
        
        try
        {
            pstmt = con.prepareStatement(freeLockers);
            pstmt.setQueryTimeout(5);
            results = pstmt.executeQuery();
            while(results.next())
            {
               lockerObj = buildLocker(results);
               list.add(lockerObj);
            }
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return list;
    }
    
    public ArrayList<Locker> getAllLockers()
    {
        ArrayList<Locker> list = new ArrayList<Locker>();
        PreparedStatement pstmt = null;
        
        String allLocker = "SELECT * FROM Locker";
        if(debug)
        {
            System.out.println(allLocker);
        }
        
        Locker lockerObj = null;
        ResultSet results;
        
        try
        {
            pstmt = con.prepareStatement(allLocker);
            pstmt.setQueryTimeout(5);
            results = pstmt.executeQuery();
            while(results.next())
            {
               lockerObj = buildLocker(results);
               list.add(lockerObj);
            }
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return list;
    }
    
    public int insertLocker(Locker lockerObj)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String insert = "INSERT INTO Locker (lockerID, available, depositum, pricePrYear, amountOfKeys, lockerType)" + "VALUES (?,?,?,?,?,?)";
        if(debug)
        {
            System.out.println(insert);
        }
        
        try
        {
            pstmt = con.prepareStatement(insert);
            pstmt.setQueryTimeout(5);
            pstmt.setInt(1, lockerObj.getLockerID());
            pstmt.setInt(2, lockerObj.getAvailable());
            pstmt.setDouble(3, lockerObj.getDepositum());
            pstmt.setDouble(4, lockerObj.getPricePrYear());
            pstmt.setInt(5, lockerObj.getAmountOfKeys());
            pstmt.setString(6, lockerObj.getLockerType());
            rc = pstmt.executeUpdate();
            
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());

        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());

        }
        return rc;
    }
    
    public int deleteLocker(int lockerID)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "DELETE FROM Locker WHERE lockerID = ?";
        if(debug)
        {
            System.out.println(delete);
        }
        
        try
        {
            pstmt = con.prepareStatement(delete);
            pstmt.setQueryTimeout(5);
            pstmt.setInt(1, lockerID);
            rc = pstmt.executeUpdate();
            
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage()); 
        }
        catch(Exception e)
        {
            e.getMessage();
        }
        return rc;
    }
        
    /**
     * Opdaterer rækken i Locker tabellen i databasen i forhold til event objektet.
     * @param lockerObj Locker objekt som er det der skal opdateres.
     * @param available Status på skabet.
     * @param depositum Depositum for at leje et skab.
     * @param pricePrYear Prisen på hvor meget det koster for at leje et skab i et år.
     * @param amountOfKeys Antal nøgler som der er givet til lejeren.
     * @param lockerType Type af skab stor eller lille.
     * @return -1 = mislykkedes. 1 = lykkedes.
     */  
    public int updateLocker(Locker lockerObj)
    {
        int rc = -1;
        String query = null;

        try
        {
            query = "UPDATE Locker " 
                  + "SET available = ?, depositum = ?, pricePrYear = ?, amountOfKeys = ?, lockerType = ? "
                  + "WHERE lockerID = ?";

            PreparedStatement preStmnt = con.prepareStatement(query);
            preStmnt.setQueryTimeout(5);
            if(debug)
            {
                System.out.println(query);
            }

            preStmnt.setInt(1, lockerObj.getAvailable());
            preStmnt.setDouble(2, lockerObj.getDepositum());
            preStmnt.setDouble(3, lockerObj.getPricePrYear());
            preStmnt.setInt(4, lockerObj.getAmountOfKeys());
            preStmnt.setString(5, lockerObj.getLockerType());
            preStmnt.setInt(6, lockerObj.getLockerID());
            rc = preStmnt.executeUpdate();
            
            preStmnt.close();
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Error: " + SQLe.getMessage());

        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("ExceptionE: " + e.getMessage());

        }        
        return rc;
    }
    
    /**
     * Bruges til at oprette en assocering fra et lockerObj til et customerObj, således at lockerObj bliver udlejet.
     * @param lockerObj lockerObj som skal udlejes.
     * @param customerObj customerObj som skal leje lockerObj.
     * @return 1 = lykkedes. -1 = mislykkedes.
     */
    public int insertLockerCustomer(Locker lockerObj, Customer customerObj)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String insert = "INSERT INTO LockerCustomer (lockerId, phoneNo) "
                      + "VALUES (?,?)";
        if(debug)
        {
            System.out.println(insert);
        }
        try
        {
            pstmt = con.prepareStatement(insert);
            pstmt.setQueryTimeout(5);
            pstmt.setInt(1, lockerObj.getLockerID());
            pstmt.setString(2, customerObj.getPhoneNo());
            
            rc = pstmt.executeUpdate();
            
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());            
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return rc;
    }       
    
    /**
     * Bruges til at slette en udlejning, således at det specfikke lockerObj ikke er udlejet mere.
     * @param lockerID lockerID på lockerObj som ikke skal udlejes mere.
     * @return 1 = lykkedes. -1 = mislykkedes.
     */
    public int deleteLockerCustomer(int lockerID)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "DELETE FROM LockerCustomer WHERE lockerID = ?";
        
        if(debug)
        {
            System.out.println(delete);
        }
        
        try
        {
            pstmt = con.prepareStatement(delete);
            pstmt.setQueryTimeout(5);
            pstmt.setInt(1, lockerID);
            rc = pstmt.executeUpdate();
            
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return rc;
    }
    
    /**
     * Opdatere et locker med en ny Customer.
     * @param lockerObj Locker som skal opdateres.
     * @param customerObj Customer som skal være den nye lejer af locker.
     * @return 1 = lykkedes. -1 = mislykkedes.
     */
    public int updateLockerCustomer(Locker lockerObj, Customer customerObj)
    {
        int rc = -1;
        
        try
        {
            String query = "UPDATE LockerCustomer "
                         + "SET phoneNo = ? "
                         + "WHERE lockerID = ?";
            if(debug)
            {
                System.out.println(query);
            }
            
            PreparedStatement pstmt = con.prepareStatement(query);
            pstmt.setQueryTimeout(5);
            
            pstmt.setString(1, customerObj.getPhoneNo());
            pstmt.setInt(2, lockerObj.getLockerID());
            
            rc = pstmt.executeUpdate();
            
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }

        return rc;
    }
   
    /**
     * Opbygning af Locker objekt sker i denne metode.
     * @param results ResultSet. En form for tabel med resultaterne i fra det
     * preparedstatement som er sendt til databasen.
     * @return Locker objekt.
     */
    private Locker buildLocker(ResultSet results)
    {
        Locker lockerObj = new Locker();
        Customer customerObj = new Customer();
                
        try  
        {   
            lockerObj.setLockerID(results.getInt("lockerID"));
            lockerObj.setAvailable(results.getInt("available"));
            lockerObj.setDepositum(results.getDouble("depositum"));
            lockerObj.setPricePrYear(results.getDouble("pricePrYear"));
            lockerObj.setAmountOfKeys(results.getInt("amountOfKeys"));
            lockerObj.setLockerType(results.getString("lockerType"));
            lockerObj.setCustomer(customerObj);
            
        }
        catch(Exception e)
            
        {
            System.out.println(e.getMessage());
            System.out.println("Error in Building Locker Object");
        }
        return lockerObj;
    }
        
    
    /**
     * PreparedStatement som bruges til find metoden ud fra eventName. Dette laves en gang og gemmes
     * derefter i en instans variabel, da dette preparedStatement bruges ofte kan
     * dette betale sig at gemme og derved går det hurigtere end hvis man skal til at
     * lave et nyt hver gang.
     * @return PreparedStatement objekt.
     */
    private PreparedStatement getPreparedStmt()
    {
        try
        {
            String query = "SELECT lockerID, available, depositum, pricePrYear, amountOfKeys, lockerType "
                         + "FROM Locker "
                         + "WHERE lockerID = ?";
            if(debug)
            {
                System.out.println("Preparestatement " + query);
            }
            pStmtSelect = con.prepareStatement(query);
            pStmtSelect.setQueryTimeout(5);
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return pStmtSelect;
    }
}